
import pandas as pd

import dolphindb as ddb
from utilities.plot_correlation import plot_distribution_by_col1
from utilities.detail_analyse_tools import detail_analyse

s = ddb.session("192.168.200.179", 8832, "chenzhimin", "suhhgjy98y_JHg87")


result = s.run("""


stk_price = select lead(close_price) as t1_close_price, lag(turnover_volume) as tm1_turnover_volume,mavg(turnover_volume, 5) as m5d_turnover_volume, lead(turnover_volume) as t1_turnover_volume, lead(change_pct) as t1_change_pct, lag(change_pct) as tm1_change_pct, move(open_price, 2) as t2_open_price, move(change_pct, 2) as t2_change_pct, move(high_price, 2) as t2_high_price, lead(high_price) as t1_high_price, lead(low_price) as t1_low_price, lead(high_price)/close_price-1 as t1_high_return, move(close_price,5) as tm_5_close_price, move(close_price,3) as tm_3_close_price, move(close_price,20) as tm_20_close_price, move(close_price, -20) as t_20_close_price, move(close_price, -10) as t_10_close_price, move(close_price, -15) as t_15_close_price, move(close_price,-5) as t5_close_price,move(close_price,-6) as t6_close_price, end_date as trading_date, iif(lead(open_price) > close_price*1.01 and lead(high_price) >=1.02*close_price,1, 0) as next_high_open,  * from loadTable("dfs://ods_stock_quotation","ods_stock_quotation") where  end_date >= 2014.01.01 and if_trading_day = 1 context by secu_code csort end_date
stk_price = select ratio(close_price, tm_5_close_price) as last_week_return, ratio(close_price, tm_3_close_price) as last_3days_return, ratio(close_price, tm_20_close_price) as last_month_return, ratio(t5_close_price, close_price) as next_week_return, ratio(t6_close_price, t1_close_price) as t1_next_week_return, ratio(t_10_close_price, close_price) as next_2week_return, ratio(t_15_close_price, close_price) as next_3week_return, ratio(t_20_close_price, close_price) as next_month_return, ratio(high_price, pre_close_price)-1 as high_return, * from stk_price
stk_deleted = select * from loadTable("dfs://XBBASE_listed_company_list", "listed_company_list") where  not(secu_abbr like '%ST%') and not(listed_sector in ('科创板', '创业板'))

stk_price = select * from ej(stk_price as s, stk_deleted as d, `secu_code) where trading_date >= 2014.01.01

B=get_indicator_data_matrix(`1,`0,`6)
x1 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_month_dde_turnover_value from loadTable("dfs://dwm_stock_factor_value_day", "dwm_stock_factor_value_day") where factor_id = `240400000001 order by end_date desc  

stk_price = select x.t1_month_dde_turnover_value, s.* from ej(stk_price as s, x1 as x, `trading_date`secu_code) 
x1 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_month_dde_turnover_value from loadTable("dfs://dwm_stock_factor_value_day", "dwm_stock_factor_value_day") where factor_id = `240400000001 order by end_date desc  
x2 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_day_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 1))
x3 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_week_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 5))
x4 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t3_month_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 60))
stk_price = select x.t1_month_dde_turnover_value, s.* from ej(stk_price as s, x1 as x, `trading_date`secu_code)
stk_price = select x.t1_day_dde_turnover_value, s.* from ej(stk_price as s, x2 as x, `trading_date`secu_code)
stk_price = select x.t1_week_dde_turnover_value, s.* from ej(stk_price as s, x3 as x, `trading_date`secu_code)
stk_price = select x.t3_month_dde_turnover_value, s.* from ej(stk_price as s, x4 as x, `trading_date`secu_code)



select * from stk_price


""")

result.to_csv('tmp.csv', index=False)

# plot_distribution_by_col1(result, 't1_month_dde_turnover_value', 't1_change_pct')
# plot_distribution_by_col1(result, 't1_day_dde_turnover_value', 't1_change_pct')
# plot_distribution_by_col1(result, 't1_week_dde_turnover_value', 't1_change_pct')
# plot_distribution_by_col1(result, 't3_month_dde_turnover_value', 't1_change_pct')
print("######################### t1_month_dde_turnover_value #########################")
detail_analyse(result, 't1_month_dde_turnover_value', 't1_change_pct', -0.1, 0.1, 10, 20)

print("######################### t1_day_dde_turnover_value #########################")
detail_analyse(result, 't1_day_dde_turnover_value', 't1_change_pct', -0.1, 0.1, 10, 20)

print("######################### t1_week_dde_turnover_value #########################")
detail_analyse(result, 't1_week_dde_turnover_value', 't1_change_pct', -0.1, 0.1, 10, 20)

print("######################### t3_month_dde_turnover_value #########################")
detail_analyse(result, 't3_month_dde_turnover_value', 't1_change_pct', -0.1, 0.1, 10, 20)

# # 确保trading_date是日期类型
# df['trading_date'] = pd.to_datetime(df['trading_date'])
#
# # 创建一个新的列来存储标准化后的值
# df['factor_value_scaled'] = 0
#
# # 对每个secu_code分组，并应用StandardScaler
# for name, group in df.groupby('secu_code'):
#     scaler = StandardScaler()
#     scaled_factor_values = scaler.fit_transform(group['factor_value'].values.reshape(-1, 1))
#     df.loc[group.index, 'factor_value_scaled'] = scaled_factor_values.flatten()
#
# # 绘制标准化后的factor_value折线图，按secu_code分组
# plt.figure(figsize=(10, 6))  # 可选：设置图形大小
# for name, group in df.groupby('secu_code'):
#     plt.plot(group['trading_date'], group['factor_value_scaled'], label=name)
#
# # 设置图例、标签和标题
# plt.legend()
# plt.xlabel('Trading Date')
# plt.ylabel('Factor Value (Scaled)')
# plt.title('Factor Value over '
#           'Time by secu_code (Scaled)')
#
# # 显示图形
# plt.show()
